# pip install openpyxl
from openpyxl import load_workbook


def open():
    wb = load_workbook('../02.py_excelFile/data01.xlsx')
    # 获取工作簿
    sh1 = wb.active
    sh2 = wb['Sheet1']
    # sh3 = wb.get_sheet_by_name('Sheet1')  # 不推荐使用该方法

    print(sh1 is sh2)


def show_sheets():
    wb = load_workbook('../02.py_excelFile/data01.xlsx')
    print(wb.sheetnames)

    for sh in wb:
        print(sh.title)


def get_one_value():
    wb = load_workbook('../02.py_excelFile/data01.xlsx')
    sh1 = wb.active

    value1 = sh1.cell(2, 3).value
    value2 = sh1['c2'].value

    print(value1, value2)


def get_many_value():
    wb = load_workbook('../02.py_excelFile/data01.xlsx')
    sh1 = wb['Sheet1']

    # 切片
    cells1 = sh1['c2':'d3']
    print(cells1)

    # 整行、整列
    cell_row3 = sh1[3]
    cell_col3 = sh1['c']
    print(cell_row3)
    print(cell_col3)

    # 获取一行/一列某一段数据
    cell_row3_5 = sh1[3:5]
    cell_col3_5 = sh1['c':'e']
    print(cell_row3_5)
    print(cell_col3_5)

    # 通过迭代获取数据
#   for row in sh1.iter_row(min_row=2, max_row=5, max_col=3):
#       for cell in row:
#           print(cell.value)


def get_all_value():
    wb = load_workbook('../02.py_excelFile/data01.xlsx')
    sh1 = wb['Sheet1']
    for row in sh1.rows:
        for cell in row:
            print(cell.value)

    for col in sh1.columns:
        for cell in col:
            print(cell.value)


def get_num():
    wb = load_workbook('../02.py_excelFile/data01.xlsx')
    sh1 = wb['Sheet1']

    print('------')
    print(sh1.max_row)
    print(sh1.max_column)


if __name__ == '__main__':
    open()
    show_sheets()
    get_one_value()
    get_many_value()
    get_all_value()
    get_num()